﻿using System;
using System.Text;
using System.Linq;
using System.Collections.Generic;
using System.Windows.Forms;
using System.Data.Odbc;
using System.Data;

namespace INHSStudentRegistration
{
    public struct LogIn
    {
        public string uname, password;
    }

    public struct UserAccount
    {
        public string UID, uaname, uauname, uapassword, cpassword, lvlofaccess, accounttype;
    }


    public struct StudentInformation
    {
       public string date, year, schoolsstatus, lrn, schoolyear, section, name, sex, dateofbirth, placeofbirth, religion,
           preaddress, contact1, proaddress, contact2,fathersname, occupation1, contact3, mothersname, occupation2, 
           contact4, guardiansname, relation1, incaseofemergency, relation2, address, contact5, 
           schoollastattended, schooladdress, noofchildreninthefamily,noiflivinginyourhouse, fps;
    }

    public  class DataBaseConnection
    {
        OdbcConnection OC = new OdbcConnection();
        OdbcDataAdapter ODA = new OdbcDataAdapter();
        public DataTable DT = new DataTable();

        public void DBConnection()
        {
            try
            {
                OC.ConnectionString = "Driver={MySQL ODBC 5.1 Driver};" +
                               "Server=localhost;" +
                               "Database=mysql;" +
                               "UID=user;" +
                                "PWD=pass";

                OC.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
           
        }

        public void Close()
        {
            OC.Close();
        }

        public void MyQuery(string mysqlcmd)
        {
            try
            {
                DT = new DataTable();
                ODA.SelectCommand = new OdbcCommand(mysqlcmd, OC);
                DT.Clear();
                ODA.Fill(DT);

            }
            catch (Exception) { }
            
        }

        public void Register_Student(StudentInformation info)
        {
            try
            {
                DataRow row = DT.NewRow();
                row[0] = info.date;
                row[1] = info.year;
                row[2] = info.schoolsstatus;
                row[3] = info.lrn;
                row[4] = info.schoolyear;
                row[5] = info.section;
                row[6] = info.name;
                row[7] = info.sex;
                row[8] = info.dateofbirth;
                row[9] = info.placeofbirth;
                row[10] = info.religion;
                row[11] = info.preaddress;
                row[12] = info.contact1;
                row[13] = info.proaddress;
                row[14] = info.contact2;
                row[15] = info.fathersname;
                row[16] = info.occupation1;
                row[17] = info.contact3;
                row[18] = info.mothersname;
                row[19] = info.occupation2;
                row[20] = info.contact4;
                row[21] = info.guardiansname;
                row[22] = info.relation1;
                row[23] = info.incaseofemergency;
                row[24] = info.relation2;
                row[25] = info.address;
                row[26] = info.contact5;
                row[27] = info.schoollastattended;
                row[28] = info.schooladdress;
                row[29] = info.noofchildreninthefamily;
                row[30] = info.noiflivinginyourhouse;
                row[31] = info.fps;

                DT.Rows.Add(row);
                OdbcCommandBuilder builder = new OdbcCommandBuilder(ODA);
                ODA.Update(DT);
            }
            catch (Exception) { }
            
        }

        public void Edit_Student_Record(StudentInformation Edit_SI, int Index)
        {
            DT.Rows[Index][0] = Edit_SI.date;
            DT.Rows[Index][1] = Edit_SI.year;
            DT.Rows[Index][2] = Edit_SI.schoolsstatus;
            DT.Rows[Index][3] = Edit_SI.lrn;
            DT.Rows[Index][4] = Edit_SI.schoolyear;
            DT.Rows[Index][5] = Edit_SI.section;
            DT.Rows[Index][6] = Edit_SI.name;
            DT.Rows[Index][7] = Edit_SI.sex;
            DT.Rows[Index][8] = Edit_SI.dateofbirth;
            DT.Rows[Index][9] = Edit_SI.placeofbirth;
            DT.Rows[Index][10] = Edit_SI.religion;
            DT.Rows[Index][11] = Edit_SI.preaddress;
            DT.Rows[Index][12] = Edit_SI.contact1;
            DT.Rows[Index][13] = Edit_SI.proaddress;
            DT.Rows[Index][14] = Edit_SI.contact2;
            DT.Rows[Index][15] = Edit_SI.fathersname;
            DT.Rows[Index][16] = Edit_SI.occupation1;
            DT.Rows[Index][17] = Edit_SI.contact3;
            DT.Rows[Index][18] = Edit_SI.mothersname;
            DT.Rows[Index][19] = Edit_SI.occupation2;
            DT.Rows[Index][20] = Edit_SI.contact4;
            DT.Rows[Index][21] = Edit_SI.guardiansname;
            DT.Rows[Index][22] = Edit_SI.relation1;
            DT.Rows[Index][23] = Edit_SI.incaseofemergency;
            DT.Rows[Index][24] = Edit_SI.relation2;
            DT.Rows[Index][25] = Edit_SI.address;
            DT.Rows[Index][26] = Edit_SI.contact5;
            DT.Rows[Index][27] = Edit_SI.schoollastattended;
            DT.Rows[Index][28] = Edit_SI.schooladdress;
            DT.Rows[Index][29] = Edit_SI.noofchildreninthefamily;
            DT.Rows[Index][30] = Edit_SI.noiflivinginyourhouse;
            DT.Rows[Index][31] = Edit_SI.fps;
        }

        public void SignIn(LogIn UA)
        {
            DataRow row = DT.NewRow();
            row[0] = UA.uname;
            row[1] = UA.password;
            
            DT.Rows.Add(row);
            OdbcCommandBuilder CB = new OdbcCommandBuilder(ODA);
            ODA.Update(DT);
        }

        public void UserAccount(UserAccount useraccount)
        {
            try
            {
                DataRow row2 = DT.NewRow();
                //row2[0] = useraccount.UID;
                row2[0] = useraccount.uaname;
                row2[1] = useraccount.uauname;
                row2[2] = useraccount.uapassword;
                row2[3] = useraccount.lvlofaccess;
                 
                DT.Rows.Add(row2);
                OdbcCommandBuilder builder = new OdbcCommandBuilder(ODA);
                ODA.Update(DT);
            }
            catch (Exception) { }
        }

        public void Update_UserAccount(UserAccount Edit_AS, int Index)
        {
            //DT.Rows[Index][0] = Edit_AS.UID;
            DT.Rows[Index][0] = Edit_AS.uaname;
            DT.Rows[Index][1] = Edit_AS.uauname;
            DT.Rows[Index][2] = Edit_AS.uapassword;
            DT.Rows[Index][3] = Edit_AS.lvlofaccess;
        }

        public void Delete_User(UserAccount delete_user)
        {
            try
            {
                DataRow row2 = DT.NewRow();
                //row2[0] = useraccount.UID;
                row2[0] = delete_user.uaname;
                row2[1] = delete_user.uauname;
                row2[2] = delete_user.uapassword;
                row2[3] = delete_user.cpassword;
                row2[4] = delete_user.lvlofaccess;

                DT.Rows.Remove(row2);
                OdbcCommandBuilder builder = new OdbcCommandBuilder(ODA);
                ODA.Update(DT);
            }
            catch (Exception) { }
        }
    }
}
